package com.example.project.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.project.entity.IssueProcessingStats;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * 需求处理统计数据Mapper接口
 */
@Mapper
public interface IssueProcessingStatsMapper {

    /**
     * 插入需求处理统计数据
     */
    @Insert("INSERT INTO t_issue_processing_stats " +
            "(issue_id, project_id, category_id, tracker_id, priority_id, author_id, assigned_to_id, " +
            "status_from, status_to, operation_type, operation_time, processing_hours) " +
            "VALUES (#{issueId}, #{projectId}, #{categoryId}, #{trackerId}, #{priorityId}, #{authorId}, #{assignedToId}, " +
            "#{statusFrom}, #{statusTo}, #{operationType}, #{operationTime}, #{processingHours})")
    int insertIssueProcessingStats(IssueProcessingStats stats);

    /**
     * 根据需求ID获取处理统计数据
     */
    @Select("SELECT * FROM t_issue_processing_stats WHERE issue_id = #{issueId} ORDER BY last_updated DESC")
    List<IssueProcessingStats> getStatsByIssueId(@Param("issueId") Integer issueId);

    /**
     * 根据项目ID获取处理统计数据
     */
    @Select("SELECT * FROM t_issue_processing_stats WHERE project_id = #{projectId} ORDER BY last_updated DESC")
    List<IssueProcessingStats> getStatsByProjectId(@Param("projectId") Integer projectId);

    /**
     * 根据需求类别获取处理统计数据
     */
    @Select("SELECT * FROM t_issue_processing_stats WHERE tracker = #{tracker} ORDER BY last_updated DESC")
    List<IssueProcessingStats> getStatsByTracker(@Param("tracker") String tracker);

    /**
     * 根据处理人获取处理统计数据
     */
    @Select("SELECT * FROM t_issue_processing_stats WHERE assigned_to_id = #{assignedToId} ORDER BY last_updated DESC")
    List<IssueProcessingStats> getStatsByAssignedToId(@Param("assignedToId") Integer assignedToId);

    /**
     * 根据时间范围获取处理统计数据
     */
    @Select("SELECT * FROM t_issue_processing_stats WHERE last_updated >= #{startTime} AND last_updated <= #{endTime}")
    List<IssueProcessingStats> getStatsByTimeRange(@Param("startTime") String startTime, @Param("endTime") String endTime);

    /**
     * 统计平均处理时间
     */
    @Select("SELECT AVG(processing_hours) as avg_hours FROM t_issue_processing_stats WHERE project_id = #{projectId} AND tracker = #{tracker}")
    Double getAvgProcessingHoursByProjectAndTracker(@Param("projectId") Integer projectId, @Param("tracker") String tracker);

    /**
     * 获取处理时间统计
     */
    @Select("SELECT MIN(processing_hours) as min_hours, MAX(processing_hours) as max_hours, AVG(processing_hours) as avg_hours " +
            "FROM t_issue_processing_stats WHERE project_id = #{projectId} AND tracker = #{tracker}")
    Map<String, Object> getProcessingTimeStats(@Param("projectId") Integer projectId, @Param("tracker") String tracker);

    /**
     * 根据项目统计处理数量
     */
    @Select("SELECT COUNT(*) FROM t_issue_processing_stats WHERE project_id = #{projectId}")
    Integer countByProjectId(@Param("projectId") Integer projectId);

    /**
     * 根据类别统计处理数量
     */
    @Select("SELECT COUNT(*) FROM t_issue_processing_stats WHERE tracker = #{tracker}")
    Integer countByTracker(@Param("tracker") String tracker);

    /**
     * 根据处理人统计处理数量
     */
    @Select("SELECT COUNT(*) FROM t_issue_processing_stats WHERE assigned_to_id = #{assignedToId}")
    Integer countByAssignedToId(@Param("assignedToId") Integer assignedToId);

    /**
     * 获取指定操作类型的处理时间
     */
    @Select("SELECT AVG(processing_hours) FROM t_issue_processing_stats WHERE issue_id = #{issueId} AND operation_type = #{fromOperationType} " +
            "AND id < (SELECT id FROM t_issue_processing_stats WHERE issue_id = #{issueId} AND operation_type = #{toOperationType} LIMIT 1)")
    BigDecimal getProcessingTimeForOperation(@Param("issueId") Long issueId, @Param("fromOperationType") Integer fromOperationType, @Param("toOperationType") Integer toOperationType);

    /**
     * 根据类别计算统计数据
     */
    @Select("SELECT " +
            "COUNT(*) as total_count, " +
            "AVG(processing_hours) as avg_processing_hours, " +
            "MIN(processing_hours) as min_processing_hours, " +
            "MAX(processing_hours) as max_processing_hours, " +
            "STDDEV(processing_hours) as stddev_processing_hours, " +
            "SUM(CASE WHEN status_to = 'completed' THEN 1 ELSE 0 END) as completed_count, " +
            "SUM(CASE WHEN status_to = 'rejected' THEN 1 ELSE 0 END) as rejected_count, " +
            "SUM(CASE WHEN operation_type = 1 THEN 1 ELSE 0 END) as create_count, " +
            "SUM(CASE WHEN operation_type = 2 THEN 1 ELSE 0 END) as update_count " +
            "FROM t_issue_processing_stats " +
            "WHERE (#{categoryId} IS NULL OR category_id = #{categoryId}) " +
            "AND (#{trackerId} IS NULL OR tracker_id = #{trackerId}) " +
            "AND (#{priorityId} IS NULL OR priority_id = #{priorityId}) " +
            "AND (#{projectId} IS NULL OR project_id = #{projectId})")
    Map<String, Object> calculateStatsByCategory(@Param("categoryId") Long categoryId, @Param("trackerId") Long trackerId, 
                                              @Param("priorityId") Long priorityId, @Param("projectId") Long projectId);

    /**
     * 查找相似的历史处理记录
     */
    @Select("SELECT * FROM t_issue_processing_stats " +
            "WHERE (#{categoryId} IS NULL OR category_id = #{categoryId}) " +
            "AND (#{trackerId} IS NULL OR tracker_id = #{trackerId}) " +
            "AND (#{priorityId} IS NULL OR priority_id = #{priorityId}) " +
            "AND (#{projectId} IS NULL OR project_id = #{projectId}) " +
            "ORDER BY processing_hours ASC " +
            "LIMIT #{limit}")
    List<IssueProcessingStats> selectSimilarIssueHistory(@Param("categoryId") Long categoryId, @Param("trackerId") Long trackerId, 
                                                        @Param("priorityId") Long priorityId, @Param("projectId") Long projectId, @Param("limit") Integer limit);

    /**
     * 获取用户的处理统计数据
     */
    @Select("SELECT " +
            "s.assigned_to_id as user_id, " +
            "u.user_name as user_name, " +
            "COUNT(*) as total_processed, " +
            "AVG(s.processing_hours) as avg_processing_hours, " +
            "MIN(s.processing_hours) as min_processing_hours, " +
            "MAX(s.processing_hours) as max_processing_hours, " +
            "SUM(CASE WHEN s.status_to = 'completed' THEN 1 ELSE 0 END) as completed_count, " +
            "SUM(CASE WHEN s.status_to = 'rejected' THEN 1 ELSE 0 END) as rejected_count, " +
            "ROUND(SUM(CASE WHEN s.status_to = 'completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as success_rate " +
            "FROM t_issue_processing_stats s " +
            "LEFT JOIN t_user u ON s.assigned_to_id = u.user_id " +
            "WHERE s.assigned_to_id = #{userId} " +
            "GROUP BY s.assigned_to_id, u.user_name " +
            "ORDER BY total_processed DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> selectUserProcessingStats(@Param("userId") Long userId, @Param("limit") Integer limit);

    /**
     * 获取类别的不同组合
     */
    @Select("SELECT " +
            "DISTINCT " +
            "s.category_id, " +
            "s.tracker_id, " +
            "s.priority_id, " +
            "s.project_id, " +
            "c.name as category_name, " +
            "t.name as tracker_name, " +
            "p.name as priority_name, " +
            "pr.name as project_name, " +
            "COUNT(*) as combination_count, " +
            "AVG(s.processing_hours) as avg_processing_hours, " +
            "MIN(s.processing_hours) as min_processing_hours, " +
            "MAX(s.processing_hours) as max_processing_hours " +
            "FROM t_issue_processing_stats s " +
            "LEFT JOIN t_issue_categorie c ON s.category_id = c.id " +
            "LEFT JOIN t_tracker t ON s.tracker_id = t.id " +
            "LEFT JOIN t_issue_priorities p ON s.priority_id = p.id " +
            "LEFT JOIN t_project pr ON s.project_id = pr.id " +
            "WHERE (#{categoryId} IS NULL OR s.category_id = #{categoryId}) " +
            "GROUP BY s.category_id, s.tracker_id, s.priority_id, s.project_id, c.name, t.name, p.name, pr.name " +
            "ORDER BY combination_count DESC")
    List<Map<String, Object>> selectCategoryCombinations(@Param("categoryId") Long categoryId);

    /**
     * 查找推荐的处理人
     */
    @Select("SELECT " +
            "s.assigned_to_id as user_id, " +
            "u.user_name as user_name, " +
            "COUNT(*) as processed_count, " +
            "AVG(s.processing_hours) as avg_processing_hours, " +
            "MIN(s.processing_hours) as min_processing_hours, " +
            "MAX(s.processing_hours) as max_processing_hours, " +
            "SUM(CASE WHEN s.status_to = 'completed' THEN 1 ELSE 0 END) as completed_count, " +
            "ROUND(SUM(CASE WHEN s.status_to = 'completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as success_rate " +
            "FROM t_issue_processing_stats s " +
            "LEFT JOIN t_user u ON s.assigned_to_id = u.user_id " +
            "WHERE (#{categoryId} IS NULL OR s.category_id = #{categoryId}) " +
            "AND (#{trackerId} IS NULL OR s.tracker_id = #{trackerId}) " +
            "AND (#{priorityId} IS NULL OR s.priority_id = #{priorityId}) " +
            "GROUP BY s.assigned_to_id, u.user_name " +
            "HAVING COUNT(*) >= 3 " +
            "ORDER BY success_rate DESC, avg_processing_hours ASC " +
            "LIMIT 1")
    Map<String, Object> findRecommendedAssignee(@Param("categoryId") Long categoryId, @Param("trackerId") Long trackerId, @Param("priorityId") Long priorityId);
}